﻿#region Using
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using PhotoGallery.Core.Business;
using CY.Utility.DBUtility;
#endregion Using

namespace PhotoGallery.DALProviders.SqlServerProvider
{
    public class LeaderProvider : PhotoGallery.Core.Providers.DALProvider.ILeaderProvider
    {
        #region Sql
        private static readonly string SqlSelectLeader = "SELECT [Id], [Name],[Description] FROM [Leader] WHERE  [Id]=@Id;";

        private static readonly string SqlInsertLeader = "INSERT INTO [Leader]([Name],[Description]) values(@Name,@Description);SELECT SCOPE_IDENTITY();";

        private static readonly string SqlUpdateLeader = "UPDATE [Leader] SET [Name] = @Name,[Description] = @Description WHERE  [Id]=@Id;";

        private static readonly string SqlDeleteLeader = "DELETE [Leader] WHERE  [Id]=@Id;";

        private static readonly string SqlGetAllLeader = "SELECT [Id], [Name],[Description] FROM [Leader];";


        private static readonly string SqlGetLeader = "SELECT [Id], [Name],[Description] FROM [Leader] where Name like '%'+@name+'%'";
        #endregion

        #region Procedures
        /*

-- Begin of Leader
IF OBJECT_ID(N'[USP_Leader_Delete_By_Id]') IS NOT NULL
	DROP PROCEDURE [USP_Leader_Delete_By_Id]

IF OBJECT_ID(N'[USP_Leader_Select_By_Id]') IS NOT NULL
	DROP PROCEDURE [USP_Leader_Select_By_Id]

IF OBJECT_ID(N'[USP_Leader_Insert]') IS NOT NULL
	DROP PROCEDURE [USP_Leader_Insert]

IF OBJECT_ID(N'[USP_Leader_Update_By_Id]') IS NOT NULL
	DROP PROCEDURE [USP_Leader_Update_By_Id]

IF OBJECT_ID(N'[USP_Leader_SelectAll]') IS NOT NULL
	DROP PROCEDURE [USP_Leader_SelectAll]

GO

--region [USP_Leader_Delete_By_Id]
CREATE PROCEDURE [USP_Leader_Delete_By_Id]
	@Id	int
AS
SET NOCOUNT ON
DELETE [Leader] WHERE  [Id]=@Id;
--endregion
GO

--region [USP_Leader_Select_By_Id]
CREATE PROCEDURE [USP_Leader_Select_By_Id]
	@Id	int
AS
SET NOCOUNT ON
SELECT [Id], [Name],[Description] FROM [Leader] WHERE  [Id]=@Id;
--endregion
GO

--region [USP_Leader_Insert]
CREATE PROCEDURE [USP_Leader_Insert]
	@Name	nvarchar(30),
	@Description	nvarchar(100)
AS
SET NOCOUNT ON
INSERT INTO [Leader]([Name],[Description]) values(@Name,@Description);SELECT SCOPE_IDENTITY();
--endregion
GO

--region [USP_Leader_Update_By_Id]
CREATE PROCEDURE [USP_Leader_Update_By_Id]
	@Name	nvarchar(30),
	@Description	nvarchar(100),
	@Id	int
AS
SET NOCOUNT ON
UPDATE [Leader] SET [Name] = @Name,[Description] = @Description WHERE  [Id]=@Id;
--endregion
GO

--region [USP_Leader_SelectAll]
CREATE PROCEDURE [USP_Leader_SelectAll]
AS
SET NOCOUNT ON
SELECT [Id], [Name],[Description] FROM [Leader];
--endregion
GO


-- End of Leader
*/
        #endregion

        #region ILeaderProvider Members
        public Core.Business.Leader Select(int id)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Id", SqlDbType.Int, id);
            SqlDataReader reader = sql.ExecuteSqlReader(SqlSelectLeader);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                Core.Business.Leader leader = new Core.Business.Leader();

                if (!reader.IsDBNull(0)) leader.Id = reader.GetInt32(0);
                if (!reader.IsDBNull(1)) leader.Name = reader.GetString(1);
                if (!reader.IsDBNull(2)) leader.Description = reader.GetString(2);

                reader.Close();
                return leader;
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                return null;
            }
        }
        public IList<Leader> GetAll()
        {
            return GetAllLeader();
        }
        public void Insert(Core.Business.Leader leader)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Name", SqlDbType.NVarChar, leader.Name);
            sql.AddParameter("@Description", SqlDbType.NVarChar, leader.Description);
            SqlDataReader reader = sql.ExecuteSqlReader(SqlInsertLeader);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                if (!reader.IsDBNull(0)) leader.Id = int.Parse(reader.GetDecimal(0).ToString());

                reader.Close();
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
            }
        }

        public void Update(Core.Business.Leader leader)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Name", SqlDbType.NVarChar, leader.Name);
            sql.AddParameter("@Description", SqlDbType.NVarChar, leader.Description);
            sql.AddParameter("@Id", SqlDbType.Int, leader.Id);
            sql.ExecuteSql(SqlUpdateLeader);
        }

        public void Delete(Core.Business.Leader leader)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Id", SqlDbType.Int, leader.Id);
            sql.ExecuteSql(SqlDeleteLeader);
        }

        public IList<Core.Business.Leader> GetAllLeader()
        {
            IList<Core.Business.Leader> leaderlist = new List<Core.Business.Leader>();
            SqlServerUtility sql = new SqlServerUtility();

            SqlDataReader reader = sql.ExecuteSqlReader(SqlGetAllLeader);

            if (reader != null)
            {
                while (reader.Read())
                {
                    Core.Business.Leader leader = new Core.Business.Leader();

                    if (!reader.IsDBNull(0)) leader.Id = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) leader.Name = reader.GetString(1);
                    if (!reader.IsDBNull(2)) leader.Description = reader.GetString(2);

                    leader.MarkOld();
                    leaderlist.Add(leader);
                }
                reader.Close();
            }
            return leaderlist;
        }
        #endregion





        public ICollection<Leader> Search(string s)
        {
            IList<Core.Business.Leader> leaderlist = new List<Core.Business.Leader>();

            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@name", SqlDbType.NVarChar, s);

            SqlDataReader reader = sql.ExecuteSqlReader(SqlGetLeader);

            if (reader != null)
            {
                while (reader.Read())
                {
                    Core.Business.Leader leader = new Core.Business.Leader();

                    if (!reader.IsDBNull(0)) leader.Id = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) leader.Name = reader.GetString(1);
                    if (!reader.IsDBNull(2)) leader.Description = reader.GetString(2);

                    leader.MarkOld();
                    leaderlist.Add(leader);
                }
                reader.Close();
            }
            return leaderlist;
        }


        ICollection<Leader> Core.Providers.DALProvider.ILeaderProvider.GetAllLeader()
        {
            return this.GetAllLeader();
        }
    }
}
